package com.F04.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F04.actionForm.F0406ActionForm;
import com.dao.common.DbOperationExt;
import com.util.CommonTools;

public class F0406Dao extends DbOperationExt{

	Logger log = Logger.getLogger(F0406Dao.class);
	
	public F0406Dao(){
		
	}
	
	public F0406Dao(Connection conn){
		super(conn);
	}
	
//	检索
	public List select(F0406ActionForm actionForm) throws SQLException{
		List list = new ArrayList();
		ResultSet rest = null;
		Map map = null;
		StringBuffer sql = new StringBuffer();
		String materialName = CommonTools.null2String(actionForm.getMaterialName());
		String materialFamily = CommonTools.null2String(actionForm.getMateralFamily());
		
		sql.append("select ms.material_code,m.name material_name,u.name measuring_unit,f.name material_family,s1.supplier supplierName1, ");
		sql.append("	price1,delivery_time_s1 ");
		sql.append(" from pc_materials_supplier ms,t_materials m,pc_material_family f,pc_suppliers s1,t_measure_units u ");
		sql.append("where m.code = ms.material_code and ");
		sql.append("	  m.family = f.code and ");
		sql.append("	  ms.supplier_code1 = s1.code and ");
		sql.append("	  m.measure_code = u.id ");
		if(materialName != null && materialName != ""){
			sql.append(" and m.name like '%"+materialName+"%' ");
		}
		if(materialFamily != null && materialFamily != ""){
			sql.append(" and f.code = '"+materialFamily+"'");
		}
		
		log.info(sql.toString());
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			map = new HashMap();
			map.put("materialCode", CommonTools.null2String(rest.getString("material_code")));
			map.put("materialName", CommonTools.null2String(rest.getString("material_name")));
			map.put("measuringUnit", CommonTools.null2String(rest.getString("measuring_unit")));
			map.put("materalFamily", CommonTools.null2String(rest.getString("material_family")));
			map.put("supplierName1", CommonTools.null2String(rest.getString("supplierName1")));
			map.put("price1", String.valueOf(rest.getInt("price1")));
			map.put("deliveryTime1", String.valueOf(rest.getInt("delivery_time_s1")));
			list.add(map);
		}
		if(map==null){
			actionForm.setErrorFlag("2");
			return null;
		}
		return list;
	}
	
//	新增
	public void insert(F0406ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		sql.append("insert into pc_materials_supplier(material_code,");
		sql.append("								supplier_code1,price1,delivery_time_s1,");
		sql.append("								supplier_code2,price2,delivery_time_s2,");
		sql.append("								supplier_code3,price3,delivery_time_s3,");
		sql.append("								supplier_code4,price4,delivery_time_s4,");
		sql.append("								description)");
		sql.append("						values ('"+actionForm.getMaterialCode()+"',");
		sql.append("							'"+actionForm.getSupplierCode1()+"','"+actionForm.getPrice1()+"',"+actionForm.getDeliveryTime1()+",");
		sql.append("							'"+actionForm.getSupplierCode2()+"','"+actionForm.getPrice2()+"',"+actionForm.getDeliveryTime2()+",");
		sql.append("							'"+actionForm.getSupplierCode3()+"','"+actionForm.getPrice3()+"',"+actionForm.getDeliveryTime3()+",");
		sql.append("							'"+actionForm.getSupplierCode4()+"','"+actionForm.getPrice4()+"',"+actionForm.getDeliveryTime4()+",");
		sql.append("							'"+CommonTools.null2String(actionForm.getDescription())+"')");
		log.info(sql.toString());
		super.executeUpdate(sql.toString());
	}
	
//	编辑初始化
	public void selectByCode(F0406ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		ResultSet rest = null;

		sql.append("select ms.material_code,m.name material_name,ms.measuring_unit,u.name measuring_unit_name,f.name material_family,f.code,s1.supplier supplierName1, ");
		sql.append("	ms.supplier_code1,price1,delivery_time_s1,ms.supplier_code2,ms.supplier_code3,ms.supplier_code4,ms.description");
		sql.append(" from pc_materials_supplier ms,t_materials m,pc_material_family f,pc_suppliers s1,t_measure_units u ");
		sql.append("where m.code = ms.material_code and ");
		sql.append("	  m.family = f.code and ");
		sql.append("	  ms.supplier_code1 = s1.code and ");
		sql.append("	  m.measure_code = u.id and ");
		sql.append("      m.code = '"+actionForm.getMaterialCode()+"'");
		
		log.info(sql.toString());
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			actionForm.setMaterialCode(CommonTools.null2String(rest.getString("material_code")));
			actionForm.setMaterialName(CommonTools.null2String(rest.getString("material_name")));
			actionForm.setMeasuringUnit(CommonTools.null2String(rest.getString("measuring_unit")));
			actionForm.setMeasuringUnitName(CommonTools.null2String(rest.getString("measuring_unit_name")));
			actionForm.setMateralFamily(CommonTools.null2String(rest.getString("code")));
			actionForm.setFamilyName(CommonTools.null2String(rest.getString("material_family")));
			actionForm.setSupplierCode1(CommonTools.null2String(rest.getString("supplier_code1")));
			actionForm.setSupplierName1(CommonTools.null2String(rest.getString("supplierName1")));
			actionForm.setPrice1(rest.getInt("price1"));
			actionForm.setDeliveryTime1(rest.getInt("delivery_time_s1"));
			actionForm.setDescription(CommonTools.null2String(rest.getString("description")));
		}
		
		sql = new StringBuffer();
		sql.append("select ms.supplier_code2,s.supplier supplierName2,price2,delivery_time_s2 ");
		sql.append(" from pc_materials_supplier ms,pc_suppliers s ");
		sql.append("where ms.supplier_code2 = s.code and ");
		sql.append("      ms.material_code = '"+actionForm.getMaterialCode()+"'");
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			actionForm.setSupplierCode2(CommonTools.null2String(rest.getString("supplier_code2")));
			actionForm.setSupplierName2(CommonTools.null2String(rest.getString("supplierName2")));
			actionForm.setPrice2(rest.getInt("price2"));
			actionForm.setDeliveryTime2(rest.getInt("delivery_time_s2"));
		}
		
		sql = new StringBuffer();
		sql.append("select ms.supplier_code3,s.supplier supplierName3,price3,delivery_time_s3 ");
		sql.append(" from pc_materials_supplier ms,pc_suppliers s ");
		sql.append("where ms.supplier_code3 = s.code and ");
		sql.append("      ms.material_code = '"+actionForm.getMaterialCode()+"'");
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			actionForm.setSupplierCode3(CommonTools.null2String(rest.getString("supplier_code3")));
			actionForm.setSupplierName3(CommonTools.null2String(rest.getString("supplierName3")));
			actionForm.setPrice3(rest.getInt("price3"));
			actionForm.setDeliveryTime3(rest.getInt("delivery_time_s3"));
		}
		
		sql = new StringBuffer();
		sql.append("select ms.supplier_code4,s.supplier supplierName4,price4,delivery_time_s4 ");
		sql.append(" from pc_materials_supplier ms,pc_suppliers s ");
		sql.append("where ms.supplier_code4 = s.code and ");
		sql.append("      ms.material_code = '"+actionForm.getMaterialCode()+"'");
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			actionForm.setSupplierCode4(CommonTools.null2String(rest.getString("supplier_code4")));
			actionForm.setSupplierName4(CommonTools.null2String(rest.getString("supplierName4")));
			actionForm.setPrice4(rest.getInt("price4"));
			actionForm.setDeliveryTime4(rest.getInt("delivery_time_s4"));
		}
	}
	
//	编辑保存
	public void update(F0406ActionForm actionForm) throws SQLException{
		StringBuffer sql = new StringBuffer();
		sql.append("update pc_materials_supplier set material_code = '"+actionForm.getMaterialCode()+"',");
		sql.append("								measuring_unit='"+actionForm.getMeasuringUnit()+"',");
		sql.append("								supplier_code1 = '"+actionForm.getSupplierCode1()+"',");
		sql.append("								price1 = "+actionForm.getPrice1()+",");
		sql.append("								delivery_time_s1 = "+actionForm.getDeliveryTime1()+",");
		sql.append("								supplier_code2 = '"+actionForm.getSupplierCode2()+"',");
		sql.append("								price2 = "+actionForm.getPrice2()+",");
		sql.append("								delivery_time_s2 = "+actionForm.getDeliveryTime2()+",");
		sql.append("								supplier_code3 = '"+actionForm.getSupplierCode3()+"',");
		sql.append("								price3 = "+actionForm.getPrice3()+",");
		sql.append("								delivery_time_s3 = "+actionForm.getDeliveryTime3()+",");
		sql.append("								supplier_code4 = '"+actionForm.getSupplierCode4()+"',");
		sql.append("								price4 = "+actionForm.getPrice4()+",");
		sql.append("								delivery_time_s4 = "+actionForm.getDeliveryTime4()+",");
		sql.append("								description = '"+actionForm.getDescription()+"' ");
		sql.append("	where material_code = '"+actionForm.getMaterialCode()+"'");
		
		log.info(sql.toString());
		super.executeUpdate(sql.toString());
	}
	
//	删除
	public void delete(F0406ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		sql.append("delete from pc_materials_supplier where material_code = '"+CommonTools.null2String(actionForm.getId())+"'");
		log.info(sql.toString());
		super.executeUpdate(sql.toString());
	}
	
	public List filterListIn5(String materialName){
		List list = new ArrayList();
		ResultSet rest = null;
		Map map = null;
		StringBuffer sql = new StringBuffer();
				
		sql.append("select distinct top 20 m.code,m.name,f.name family,u.name measuringUnit,m.description description ");
		sql.append("from t_materials m, pc_material_family f,t_measure_units u ");
		sql.append("where m.family = f.code and m.measure_code = u.id and m.name like '%"+materialName+"%'");
		log.info(sql.toString());
		try {
			rest = super.executeQuery(sql.toString());
			while(rest.next()){
				map = new HashMap();
				map.put("value1", CommonTools.null2String(rest.getString("code")));
				map.put("value2", CommonTools.null2String(rest.getString("name")));
				map.put("value3", CommonTools.null2String(rest.getString("family")));
				map.put("value4", CommonTools.null2String(rest.getString("measuringUnit")));
				map.put("value5", CommonTools.null2String(rest.getString("description")));
				list.add(map);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
